package zz.extjs.impl;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONObject;

import zz.extjs.dao.CrossSchemaDAO;
import zz.extjs.dbc.DataBaseConnection;
import zz.extjs.factory.DAOFactory;
import zz.extjs.vo.Student;

/**
 * 处理跨schema的数据处理，即查询历年成绩汇总查询
 * 
 * @author Administrator
 * 
 */
public class CrossSchemaDAOImpl implements CrossSchemaDAO {

	/**
	 * 得到当前存在的数据库方案，即学期列表，like:'1001','1002','0601'...
	 * 
	 * @return
	 * @throws Exception
	 */
	public JSONArray getSchList() throws Exception {

		JSONArray js = new JSONArray();
		try {
			String curname = DAOFactory.getDatasetDAOInstance().getDBUsername();
			DataBaseConnection dbc = new DataBaseConnection();
			Statement stmt = dbc.getConnection().createStatement();
			for (int i = 1; i < 40; i++) { // 解析4位数字编号，‘1001,1002,0502,0601’...
				String term = String.format("%1$02d" + "%2$02d", i / 2 + 5,
						i % 2 + 1); // //装配学期，‘0502’开始，term='0501','06'

				int r = -1;
				try {
					ResultSet rs = stmt.executeQuery("select * from dusc"
							+ term + ".PERSON_INFO_TAB");
					while (rs.next())
						r++;
				} catch (Exception e) {
					r = -1;
				}
				if (r > -1)
					js.put(new JSONObject("{text:'" + term + "'}"));
				if (curname.indexOf(term) >= 0)
					break;
			}
		} catch (Exception e) {
			throw new Exception("数据库操作失败");
		} finally {

		}
		return js;

	}

	/**
	 * 根据不同数据库，改变临时表访问方法
	 */
	public void changSql(DataBaseConnection dbc, Statement stmt,
			JSONObject sqljo) throws Exception {
		String strdrv = dbc.getConnection().toString();
		int r = -1;
		if (strdrv != null && strdrv.indexOf("oracle") >= 0) {
			// 检查是连接oracle还是Derby,对oracle需先检查临时表是否存在，存在则只清空，而不创建
			try {
				r = stmt.executeUpdate(sqljo.getString("sqlselect_oracle"));
			} catch (Exception e) {
				r = -1;
			}
			if (r < 0) {
				sqljo.put("sqlcreate", sqljo.getString("sqlcreate_oracle"));
			} else
				sqljo.put("sqlcreate", "CRS_STU_TEMP");

			sqljo.put("sqlinsert", sqljo.getString("sqlinsert_oracle"));
			sqljo.put("sqlselect", sqljo.getString("sqlselect_oracle"));
		} else if (strdrv != null && strdrv.indexOf("derby") >= 0) {
			try {
				r = stmt.executeUpdate(sqljo.getString("sqlselect_derby"));
			} catch (Exception e) {
				r = -1;
			}
			if (r < 0) {
				sqljo.put("sqlcreate", sqljo.getString("sqlcreate_derby"));
			} else
				sqljo.put("sqlcretate", "CRS_STU_TEMP");

			sqljo.put("sqlinsert", sqljo.getString("sqlinsert_derby"));
			sqljo.put("sqlselect", sqljo.getString("sqlselect_derby"));

		}

	}

	/**
	 * 得到所有学期的学生汇总，其中包含学生读相应课程的时间
	 */
	public Iterator<JSONObject> getAllTermList(String sqlwhere) throws Exception {

		List<JSONObject> result = new ArrayList<JSONObject>();

		JSONArray jschlst = getSchList(); // 得到学期列表
		int r = -1;
		String sql = "";
		String str = "";
		for (int j = 0; j < jschlst.length(); j++)
			str += ",#" + ((JSONObject) jschlst.get(j)).getString("text")
					+ "@"; // ,#"0702"@,#"1001"@,#"1002"@
		//System.out.println("term:" + str);
		JSONObject sqljo = new JSONObject();
		sqljo
				.put(
						"sqlcreate_derby",
						"declare global temporary table  Session.CRS_STU_TEMP("
								+ "PER_ID VARCHAR(32),T_SID VARCHAR(8),E_NAME VARCHAR(50),T_PBOUT VARCHAR(25),T_PBBEGIN DATE,"
								+ "C_NAME VARCHAR(16),K_NAME VARCHAR(16),PB_ID VARCHAR(16),T_BIRTHDAY DATE,"
								+ "T_SEX VARCHAR(8),MEMO VARCHAR(255),PB_TYPE VARCHAR(16),PASS_ID VARCHAR(8),"
								+ "T_PHONE VARCHAR(16),T_CLASS VARCHAR(8),T_STUDY VARCHAR(64),COS_ID VARCHAR(32)"
								+ str.replaceAll("@", " VARCHAR(32)")
										.replaceAll("#", "t") // ,"t1001" VARCHAR(32),..
								+ ") ON COMMIT PRESERVE ROWS  not logged");
		sqljo
				.put(
						"sqlinsert_derby",
						"insert into Session.CRS_STU_TEMP("
								+ "PER_ID, T_SID, E_NAME,C_NAME, K_NAME, PB_ID,T_BIRTHDAY, T_SEX,T_PHONE, MEMO, PB_TYPE, PASS_ID, "
								+ "T_CLASS,T_STUDY,COS_ID, T_PBOUT,T_PBBEGIN"
								+ str.replaceAll("#", "t").replaceAll("@", "")// ,t0702,t0601,....
								+ ") SELECT PER_ID, T_SID, E_NAME,C_NAME, K_NAME, PB_ID,T_BIRTHDAY, T_SEX,T_PHONE, MEMO, PB_TYPE, "
								+ "PASS_ID, T_CLASS,T_STUDY, c.COS_ID,T_PBOUT,T_PBBEGIN"
								+ str.replaceAll("#", "'-' t").replaceAll(
										"@", "") // ,'null' "0702",'null'
													// "0601",....
								+ " FROM "); // 执行查询时动态装配
		sqljo.put("sqlselect_derby", "select * from Session.CRS_STU_TEMP ");


		sqljo
				.put(
						"sqlcreate_oracle",
						"CREATE GLOBAL TEMPORARY TABLE CRS_STU_TEMP ("
								+ "PER_ID VARCHAR2(32 CHAR),T_SID VARCHAR2(8 CHAR),E_NAME VARCHAR2(50 CHAR),"
								+ "C_NAME VARCHAR2(16 CHAR),K_NAME VARCHAR2(16 CHAR),PB_ID VARCHAR2(16 CHAR),T_BIRTHDAY DATE,"
								+ "T_SEX VARCHAR2(8 CHAR),MEMO VARCHAR2(255 CHAR),PB_TYPE VARCHAR2(16 CHAR),PASS_ID VARCHAR2(8 CHAR),"
								+ "T_PHONE VARCHAR2(16 CHAR),T_CLASS VARCHAR2(8 CHAR),T_PBOUT VARCHAR2(25 CHAR),T_PBBEGIN DATE,"
								+ "T_STUDY VARCHAR2(64 CHAR),COS_ID VARCHAR2(32 CHAR)"
								+ str.replaceAll("@", " VARCHAR(32 CHAR)")
										.replaceAll("#", "t") // , t1001 VARCHAR(32),..
								+ ")ON COMMIT PRESERVE ROWS");
		sqljo
				.put(
						"sqlinsert_oracle",
						"insert into CRS_STU_TEMP("
								+ "PER_ID, T_SID, E_NAME,C_NAME, K_NAME, PB_ID,T_BIRTHDAY, T_SEX,T_PHONE, MEMO, PB_TYPE, PASS_ID, "
								+ "T_CLASS,T_STUDY, COS_ID, T_PBOUT,T_PBBEGIN"
								+ str.replaceAll("#", "t").replaceAll("@", "")// ,t0702,t0601,....
								+ ") SELECT PER_ID, T_SID, E_NAME,C_NAME, K_NAME, PB_ID,T_BIRTHDAY, T_SEX,T_PHONE, MEMO, PB_TYPE, "
								+ "PASS_ID, T_CLASS,T_STUDY, c.COS_ID, T_PBOUT,T_PBBEGIN"
								+ str.replaceAll("#", "'-' t").replaceAll(
										"@", "") // ,'null' t0702,....
								+ " FROM "); // 执行查询时动态装配
		sqljo.put("sqlselect_oracle", "select * from CRS_STU_TEMP ");
		// System.out.println(sqljo.getString("sqlcreate_oracle"));
		// System.out.println(sqljo.getString("sqlinsert_oracle"));

		sqljo.put("sqlcretate", "");
		sqljo.put("sqlinsert", "");
		sqljo.put("sqlselect", "");

		DataBaseConnection dbc = null;
		try {
			dbc = new DataBaseConnection();
			// dbc.setConnection(conn);
			Statement stmt = dbc.getConnection().createStatement();

			changSql(dbc, stmt, sqljo);

			if ("CRS_STU_TEMP".equals(sqljo.getString("sqlcreate"))) // 在orcale时，省略创建临时表步骤
				sql = "delete from CRS_STU_TEMP";
			else
				sql = sqljo.getString("sqlcreate");
			 //System.out.println(sql);
			stmt.executeUpdate(sql);

			/** ************insert data*********************** */
			sql = ""; // ".... from "
			for (int i = 0; i < jschlst.length(); i++) {
				String s = ((JSONObject) jschlst.get(i)).getString("text");
				sql = sqljo.getString("sqlinsert").replaceFirst(
						"'-' t" + s , "c.COS_NAME t" + s );
				sql += " DUSC" + s + ".PERSON_INFO_TAB p left join DUSC" + s
						+ ".COURSE_TAB c on p.COS_ID=c.COS_ID ";

				 //System.out.println(sql);
				stmt.executeUpdate(sql);

			}
			/** *********************************** */

			/** ************out put*********************** */
			sql = sqljo.getString("sqlselect");		//select * from CRS_STU_TEMP group by T_SID order by T_SID
			String tmp ="T_SID,max(PER_ID) PER_ID,max(E_NAME) E_NAME,max(C_NAME) C_NAME,max(K_NAME) K_NAME, " +
					"max(PB_ID) PB_ID,max(T_BIRTHDAY) T_BIRTHDAY,max(T_SEX) T_SEX,max(T_PHONE) T_PHONE,max(MEMO) MEMO," +
					"max(PB_TYPE) PB_TYPE,max(T_STUDY) T_STUDY " ;
				// "select ...,max("1001") "1001",...from Session.CRS_STU_TEMP group by T_SID order by T_SID"
			
			for (int i = 0; i < jschlst.length(); i++) {
				String s = ((JSONObject) jschlst.get(i)).getString("text");	//"1001"
				tmp += ",max(t"+s+") t"+s+"";		//,max(t1001) t1001,max(t1002)
			}
			sql = sql.replace("*", tmp)+" group by T_SID order by T_SID desc ";
			//System.out.println("output select:" + sql);
			/** *********************************** */

			/*****************添加分页处理，标志是参数sqlwhere含有“where"*****************/
			if(sqlwhere!=null && sqlwhere.indexOf("where")>=0){
				sql = "select * from (select rownum n,p.* from ( " + sql + " ) p " + sqlwhere;
			}
			
			ResultSet rs = stmt.executeQuery(sql);

			r = 0;
			//rs.beforeFirst();
			while (rs.next()) { // 有记录返回
				JSONObject jObject = new JSONObject();
				//,"pass_id":null,,"course":"基本课程"},
				jObject.put("sid", rs.getString("T_SID"));
				//jObject.put("pb_id", rs.getString("PER_ID"));
				jObject.put("ename", rs.getString("E_NAME"));
				jObject.put("cname", rs.getString("C_NAME"));
				jObject.put("kname", rs.getString("K_NAME"));
				jObject.put("pb_id", rs.getString("PB_ID"));
				jObject.put("birthday", rs.getString("T_BIRTHDAY"));
				jObject.put("sex", rs.getString("T_SEX"));
				jObject.put("phone", rs.getString("T_PHONE"));
				jObject.put("pb_type", rs.getString("PB_TYPE"));
				jObject.put("study", rs.getString("T_STUDY"));
				jObject.put("memo", rs.getString("MEMO"));
				for (int i = 0; i < jschlst.length(); i++) {
					String s = ((JSONObject) jschlst.get(i)).getString("text");	//"1001"
					jObject.put("t"+s, "NULL".equals(rs.getString("t"+s)) ? null : rs.getString("t"+s));		
					//System.out.println("t"+s+":"+rs.getString("t"+s));
				}	
				
				result.add(jObject);
				r++;
				//System.out.println(jObject);				
			}

			System.out.println("recodset count:" + r);
		} catch (Exception e1) {
			//System.err.println("execsql.executeUpdate:" + e1.getMessage());
		} finally {
			dbc.close();
		}

		return result.iterator();
	}

	/**
	 * 用于跨方案的学生历年学习记录查询
	 * 
	 * @param sql
	 * @return 返回学生基本资料和何年参加和课程的记录集，不涉及具体成绩等
	 * @throws Exception
	 */
	public Iterator<Student> getCrossSelection(String sql) throws Exception {
		List<Student> result = new ArrayList<Student>();
		// 先查询历年学生记录，返回学号，课程
		sql = "select * from DUSC1001.PERSON_INFO_TAB p left join DUSC1001.COURSE_TAB c on p.COS_ID=c.COS_ID";
		// result.addAll(getList(sql));
		return result.iterator();
	}
}